package com.sophie.dao.impl;

import com.sophie.DTO.ZoneDTO;
import com.sophie.dao.BaseDAO;
import com.sophie.dao.IZoneDAO;
import com.sophie.entity.Zone;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

/**
 * @Author: Rowena
 * @CreateTime: 2024-09-10 16:32:55
 * @Version: 1.0
 */

public class ZoneDAOImpl extends BaseDAO implements IZoneDAO {
    @Override
    public int addZone(Zone zone) {
        try {
            String sql="insert into zone(contenttext,publishtime,status,userid,imgs) values(?,?,?,?,?)";
            int update = super.update(sql, zone.getContenttext(), zone.getPublishtime(), zone.getStatus(),zone.getUserid(), zone.getImgs());
            return update;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<ZoneDTO> getZonesByUserId(Integer id) {
        try {
            String sql="SELECT userid,`user`.`name` AS username,motto,avatar,zone.id AS zoneid,contenttext,goodcount,transmitcount,commentcount,publishtime,imgs  " +
                    "FROM zone,`user` " +
                    "WHERE zone.userid=`user`.id AND `user`.id=? ORDER BY zone.publishtime DESC ";
            List<ZoneDTO> all = super.getAll(sql, ZoneDTO.class,id);
            return all;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public long countZonesByUserId(Integer id) {
        try {
            String sql="select count(*) from zone where userid=? and status=1";
            Long count= super.getScalar(sql, id);
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<ZoneDTO> getAllZones() {
        try {
            String sql="select userid,user.name as username,motto,avatar,zone.id as zoneid,contenttext,goodcount,transmitcount,commentcount,publishtime,imgs " +
                    "FROM zone,`user`  " +
                    "where zone.userid=`user`.id " +
                    "order by zone.publishtime desc";
            List<ZoneDTO> zones = super.getAll(sql, ZoneDTO.class);
            return zones;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public long countTransmit(Integer id) {
        try {
            String sql="update zone set transmitcount=COALESCE(transmitcount, 0) + 1 where id=?";
            int update = super.update(sql, id);
            String sql2="select transmitcount from zone where id=?";
            Long scalar = super.getScalar(sql2, id);
            return scalar;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int checkCollected(Integer zoneid,Integer userid) {
        try {
            String sql="select 1 from commend where zoneid=? and userid=? limit 1;";
            Long l = super.getScalar(sql, zoneid,userid);
            if (l==1){
                return 1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public long collectZone(Integer zoneid,Integer userid) {
        try {
            String sql="insert into commend(userid,zoneid,commendtime) values(?,?,?);";
            int i = super.update(sql,  userid, zoneid,new Date());
            if(i>0){
                String sql2="update zone set goodcount=COALESCE(goodcount, 0) + 1 where id=?";
                int update = super.update(sql2, zoneid);
                String sql3="select count(id) from commend where zoneid=? and userid=?";
                Long scalar = super.getScalar(sql3, zoneid,userid);
                if (update+scalar>1){
                    return scalar;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int cancelZone(Integer zoneid, Integer userid) {
        try {
            String sql="delete from commend where userid=? and zoneid=?;";
            int d = super.update(sql, userid, zoneid);
            if (d>0){
                String sql2="update zone set goodcount=COALESCE(goodcount, 0) - 1 where id=?";
                int update = super.update(sql2, zoneid);
                if (update>0){
                    return update;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
